package com.xbniao.gcfs.common.util.ExportExcel;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.math.BigDecimal;

/**
 * @Author:wangbin.
 * @Description.
 * @Date:Created in 14:09 2018/4/27.
 * @Modified By:
 */
public class POIExportExcelUtil {

    /**
     * 字符串显示   处理null的情况
     * @param info
     * @return
     */
    public static String showStringInfo(String info)
    {
        if(StringUtils.isEmpty(info))
            return "";
        return info;
    }

    /**
     * 获取某个sheet中某一行的高度值
     * @param sheet
     * @param rowIndex  所在行的索引 从0开始数
     * @return
     */
    public static short getRowHeight(Sheet sheet, int rowIndex)
    {
        Row row = sheet.getRow(rowIndex);
        return row.getHeight();
    }

    /**
     * 设置某个sheet中某一行的高度值
     * @param sheet
     * @param rowIndex  所在行的索引 从0开始数
     * @param height    要设置的高度
     */
    public static void setRowHeight(Sheet sheet, int rowIndex, short height)
    {
        Row row = sheet.getRow(rowIndex);
        row.setHeight(height);
    }

    /**
     * 修改单元格的内容
     * @param sheet
     * @param rowIndex     从0行开始数
     * @param cellIndex    从0列开始数
     * @param value        要修改的值
     */
    public static void updateCellValue(Sheet sheet, int rowIndex, int cellIndex, Object value)
    {
        Row row = sheet.getRow(rowIndex);
        Cell cell = row.getCell(cellIndex);//得到列
        if(value == null)
        {
            cell.setCellValue("");
            return;
        }
        if(value instanceof String)
            cell.setCellValue(value.toString());
        else if(value instanceof Integer)
            cell.setCellValue(Integer.parseInt(value.toString()));
        else if(value instanceof BigDecimal)
        {
            BigDecimal bigDecimal = new BigDecimal(value.toString());
            cell.setCellValue(bigDecimal.doubleValue());
        }
        else
            cell.setCellValue(value.toString());
    }

    /**
     * 找到需要插入的行数，并新建一个POI的row对象
     * @param sheet
     * @param rowIndex
     * @return
     */
    public static Row createRow(Sheet sheet, Integer rowIndex) throws Exception{
        Row row = null;
        if (sheet.getRow(rowIndex) != null) {
            int lastRowNo = sheet.getLastRowNum();
            sheet.shiftRows(rowIndex, lastRowNo, 1);
        }
        row = sheet.createRow(rowIndex);
        return row;
    }

    /**
     * 复制一行 并插入到指定位置
     * @param startRow    复制开始行
     * @param endRow      复制结束行
     * @param pPosition   要插入的行位置
     * @param sheet
     */
    public static void copyRows(int startRow, int endRow, int pPosition,
                                Sheet sheet) {
        int pStartRow = startRow - 1;
        int pEndRow = endRow - 1;
        int targetRowFrom;
        int targetRowTo;
        int columnCount;
        CellRangeAddress region = null;
        int i;
        int j;
        if (pStartRow == -1 || pEndRow == -1) {
            return;
        }
        // 拷贝合并的单元格
        for (i = 0; i < sheet.getNumMergedRegions(); i++) {
            region = sheet.getMergedRegion(i);
            if ((region.getFirstRow() >= pStartRow)
                    && (region.getLastRow() <= pEndRow)) {
                targetRowFrom = region.getFirstRow() - pStartRow + pPosition;
                targetRowTo = region.getLastRow() - pStartRow + pPosition;
                CellRangeAddress newRegion = region.copy();
                newRegion.setFirstRow(targetRowFrom);
                newRegion.setFirstColumn(region.getFirstColumn());
                newRegion.setLastRow(targetRowTo);
                newRegion.setLastColumn(region.getLastColumn());
                sheet.addMergedRegion(newRegion);
            }
        }
        int lastRowNo = sheet.getLastRowNum();
        sheet.shiftRows(pPosition, lastRowNo, 1);
        // 设置列宽
        for (i = pStartRow; i <= pEndRow; i++) {
            Row sourceRow = sheet.getRow(i);
            columnCount = sourceRow.getLastCellNum();
            if (sourceRow != null) {
                Row newRow = sheet.createRow(pPosition - pStartRow + i);
                newRow.setHeight(sourceRow.getHeight());
                for (j = 0; j < columnCount; j++) {
                    Cell templateCell = sourceRow.getCell(j);
                    if (templateCell != null) {
                        Cell newCell = newRow.createCell(j);
                        copyCell(templateCell, newCell);
                    }
                }
            }
        }
    }


    public static void copyCell(Cell srcCell, Cell distCell) {
        distCell.setCellStyle(srcCell.getCellStyle());
        if (srcCell.getCellComment() != null) {
            distCell.setCellComment(srcCell.getCellComment());
        }
        int srcCellType = srcCell.getCellType();
        distCell.setCellType(srcCellType);
        if (srcCellType == HSSFCell.CELL_TYPE_NUMERIC) {
            if (HSSFDateUtil.isCellDateFormatted(srcCell)) {
                distCell.setCellValue(srcCell.getDateCellValue());
            } else {
                distCell.setCellValue(srcCell.getNumericCellValue());
            }
        } else if (srcCellType == HSSFCell.CELL_TYPE_STRING) {
            distCell.setCellValue(srcCell.getRichStringCellValue());
        } else if (srcCellType == HSSFCell.CELL_TYPE_BLANK) {

        } else if (srcCellType == HSSFCell.CELL_TYPE_BOOLEAN) {
            distCell.setCellValue(srcCell.getBooleanCellValue());
        } else if (srcCellType == HSSFCell.CELL_TYPE_ERROR) {
            distCell.setCellErrorValue(srcCell.getErrorCellValue());
        } else if (srcCellType == HSSFCell.CELL_TYPE_FORMULA) {
            distCell.setCellFormula(srcCell.getCellFormula());
        } else {

        }
    }
}
